/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("cw_yszk_view",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using VOL.Finance.IServices;
using OfficeOpenXml;
using System.IO;
using sysio = System.IO;
using sysnet = System.Net;
using System.Linq;
using VOL.Core.Extensions;
using VOL.Core.Utilities;
using VOL.Core.DBManager;
using OfficeOpenXml.Style;
using VOL.Core.Filters;

namespace VOL.Finance.Controllers
{
    public partial class cw_yszk_viewController
    {
        private readonly Icw_yszk_viewService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;

        [ActivatorUtilitiesConstructor]
        public cw_yszk_viewController(
            Icw_yszk_viewService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }

        [ApiActionPermission()]
        public override ActionResult Export([FromBody] PageDataOptions loadData)
        {
            WebResponseContent webResponse = new WebResponseContent();
            String jsonstr = loadData.Wheres;
            List<SearchParameters> parameters = loadData.Wheres.DeserializeObject<List<SearchParameters>>();
            string tsql = "SELECT CONVERT(VARCHAR(7),duedate,121) AS duedate,trackname,unit,personliable,yearearly,stockCategory,newinvoicereceivable,newreceivablepad,addCategory,totalCategory,lastmonthstock,stockchange,newsaletax,newsalerate,yearoverdue,stockIsOverdue,stockIsOverdueCushionCushionType,stockIsOverdueCushionCushionType1,stockIsOverdueCushionCushionType2,total,lastmonthoverdue,newoverduereson,solutionmeasure FROM cw_yszk_view where 1=1 ";
            String ysrq = "";
            foreach (SearchParameters a in parameters)
            {
                if (a.Name.Equals("duedate"))
                {
                    ysrq = a.Value;
                    tsql = tsql + " and CONVERT(VARCHAR(7),duedate,121) =  " + "\'" + Convert.ToString(a.Value) + "\'";
                }
                else
                {
                    tsql = tsql + " and " + a.Name + " = " + "\'" + Convert.ToString(a.Value) + "\'";
                }
            }
            List<dynamic> list = DBServerProvider.SqlDapper.QueryList<dynamic>(tsql, null).ToList();
            string fileName = "应收账款.xlsx";
            string fullFileName = ExportYszkfcExcel1(list, ysrq, fileName);
            return File(sysio.File.ReadAllBytes(fullFileName), sysnet.Mime.MediaTypeNames.Application.Octet, fileName);
        }
        private String ExportYszkfcExcel1(List<dynamic> list, String ysrq, string fileName)
        {
            string path = null;
            path = path ?? $"Download/ExcelExport/{DateTime.Now.ToString("yyyyyMMdd")}/";
            string fullPath = path.MapPath();
            fileName = Guid.NewGuid() + "_" + fileName;
            if (!Directory.Exists(fullPath)) Directory.CreateDirectory(fullPath);
            ExcelPackage excel = new ExcelPackage();
            ExcelWorksheet sheet = excel.Workbook.Worksheets.Add("sheet1");
            sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            int thisMonth = Convert.ToInt32(ysrq.Substring(5, 2));
            sheet.Cells["A1"].Value = "应收日期";
            sheet.Cells["A1:A2"].Merge = true;
            sheet.Cells["B1"].Value = "赛道";
            sheet.Cells["B1:B2"].Merge = true;
            sheet.Cells["C1"].Value = "单位";
            sheet.Cells["C1:C2"].Merge = true;
            sheet.Cells["D1"].Value = "责任人";
            sheet.Cells["D1:D2"].Merge = true;
            sheet.Cells["E1"].Value = "年初应收";
            sheet.Cells["E1:E2"].Merge = true;
            sheet.Cells["F1"].Value = thisMonth + "月末应收(万元)";
            sheet.Cells["F1:N1"].Merge = true;
            sheet.Cells["F2"].Value = "存量";
            sheet.Cells["G2"].Value = "新增应收开票";
            sheet.Cells["H2"].Value = "新增应收代垫";
            sheet.Cells["I2"].Value = "新增应收";
            sheet.Cells["J2"].Value = "应收合计";
            sheet.Cells["K2"].Value = "上月存量";
            sheet.Cells["L2"].Value = "存量环比变动";
            sheet.Cells["M2"].Value = "新增含税销售额";
            sheet.Cells["N2"].Value = "新增销售回款率";
            sheet.Cells["O1"].Value = "年初逾期";
            sheet.Cells["O1:O2"].Merge = true;
            sheet.Cells["P1"].Value =  thisMonth + "月末逾期应收(万元)";
            sheet.Cells["P1:U1"].Merge = true;
            sheet.Cells["P2"].Value = "存量逾期";
            sheet.Cells["Q2"].Value = "新增逾期开票";
            sheet.Cells["R2"].Value = "新增逾期保兑仓代垫";
            sheet.Cells["S2"].Value = "新增逾期终端代垫";
            sheet.Cells["T2"].Value = "逾期合计";
            sheet.Cells["U2"].Value = "上月逾期";
            sheet.Cells["V1"].Value = "新增逾期分析及举措";
            sheet.Cells["V1:W1"].Merge = true;
            sheet.Cells["V2"].Value = "新增逾期原因";
            sheet.Cells["W2"].Value = "解决措施";
            
            for (int i = 0; i < list.Count(); i++)
            {
                int j = 0;
                int rowIndex = i + 3;
                int columnIndex = j + 1;
                foreach (KeyValuePair<string, object> col in list[i])
                {
                    string aa = col.Key;//属性
                    if (col.Value == null || col.Value.IsNullOrEmpty() || col.Value.Equals("null"))
                    {
                        sheet.Cells[rowIndex, columnIndex].Value = "";
                    }
                    else
                    {
                        string bb = col.Value.ToString() ?? "";//值
                        sheet.Cells[rowIndex, columnIndex].Value = bb;
                    }
                    columnIndex++;
                }
            }
            FileInfo file = new FileInfo(fullPath + fileName);
            excel.SaveAs(file);
            return fullPath + fileName;
        }
    }
}
